﻿/*
 Copyright © BlazeApps 2006.  All rights reserved.
 Visit www.BlazeApps.com for more information about us.
 */

/*
 * MySqlPersonalizationProvider class
 * Author : Kris Booghmans
 * Company: PXLStudio (www.pxlstudio.be)
 * Version: 1.0
 * Date   : 06/09/2006
 * Note   : Based on AccessProviders by Microsoft corporation.
 */

/*
 * Author : Harry Goble
 * Company: Blaze Apps (www.blazeapps.com)
 * Version: 1.1
 * Date   : 12/04/2007
 * Notes  : Updated the tables to use the ApplicationID as well as the UserID to make this set of
 * providers act more like the SQL Server ones.  Basically it meant adding some missing fields. I also 
 * changed the int ID values to guid values.  This was done for replication reason and to ensure a 
 * true unique ID was being used versus a integer value.
 */

namespace BA.MySqlProviders {

    using System;
    using System.Collections;
    using System.Collections.Specialized;
    using System.ComponentModel;
    using System.Configuration.Provider;
    using System.Globalization;
    using System.Data;
    using MySql.Data.MySqlClient;
    using MySql.Data.Types;
    using System.Security.Permissions;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.Security;
    using System.Web.Util;
    using System.Web.DataAccess;

    /// <devdoc>
    /// The provider used to MySql the personalization store for WebPart pages from an PersonalWebsiteTest
    /// database.
    /// </devdoc>
    public sealed class MySqlPersonalizationProvider : PersonalizationProvider {

        private const int MaxStringLength = 255;

        private string _applicationName;
        private Guid _applicationID = Guid.NewGuid();
        private DateTime _applicationIDCacheDate;
        private string _ConnectionStringName;

        /// <devdoc>
        /// Initializes an instance of MySqlPersonalizationProvider.
        /// </devdoc>
        public MySqlPersonalizationProvider() {
        }

        public override string ApplicationName {
            get {
                if (String.IsNullOrEmpty(_applicationName)) {
                    _applicationName = SecUtility.GetDefaultAppName();
                }
                return _applicationName;
            }
            set {
                if (value != null && value.Length > MaxStringLength) {
                    throw new ProviderException("ApplicationName exceeded max length of " + MaxStringLength);
                }
                _applicationName = value;
            }
        }

        private MySqlParameter CreateDateTimeMySqlParameter(string parameterName, DateTime dt) {
            MySqlParameter p = new MySqlParameter(parameterName, MySqlDbType.Timestamp);
            p.Direction = ParameterDirection.Input;
            p.Value = MySqlConnectionHelper.RoundToSeconds(dt);
            return p;
        }

        private byte[] Deserialize(string data) {
            if (String.IsNullOrEmpty(data)) {
                return null;
            }
            return Convert.FromBase64String(data);
        }

        private Guid GetApplicationID(MySqlConnectionHolder holder)
        {
            if (_applicationID != Guid.Empty && holder.CreateDate < _applicationIDCacheDate) {
                return _applicationID;
            }

            string appName = ApplicationName;
            if (appName.Length > MaxStringLength) {
                appName = appName.Substring(0, MaxStringLength);
            }

            _applicationID = MySqlConnectionHelper.GetApplicationID(holder.Connection, appName, true);
            _applicationIDCacheDate = DateTime.Now;

            if(_applicationID != Guid.Empty)
            {
                throw new ProviderException("Failed to get ApplicationID");
            }

            return _applicationID;
        }

        private PersonalizationStateInfoCollection FindSharedState(string path,
                                                                   int pageIndex,
                                                                   int pageSize,
                                                                   out int totalRecords) {
            const string findSharedState =
                "SELECT Paths.Path, AllUsers.LastUpdatedDate, LEN(AllUsers.PageSettings)" +
                " FROM aspnet_PagePersonalizationAllUsers AllUsers, aspnet_Paths Paths" +
                " WHERE AllUsers.PathId = Paths.PathId AND Paths.ApplicationId = ?ApplicationId";
            const string orderBy = " ORDER BY Paths.Path ASC";
            const string findUserState =
                "SELECT SUM(LEN(PerUser.PageSettings)), COUNT(*)" +
                " FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Paths Paths" +
                " WHERE PerUser.PathId = Paths.PathId" +
                " AND Paths.ApplicationId = ?ApplicationId" +
                " AND Paths.Path LIKE ?Path";

            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            MySqlDataReader reader = null;
            totalRecords = 0;

            try {
                try {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;
                    MySqlCommand command = new MySqlCommand(findSharedState, connection);
                    MySqlParameterCollection parameters = (MySqlParameterCollection) command.Parameters;
                    MySqlParameter parameter;

                    Guid appId = GetApplicationID(connectionHolder);
                    parameter = parameters.Add("ApplicationId", MySqlDbType.VarChar);
                    parameter.Value = appId;

                    if (path != null) {
                        command.CommandText += " AND Paths.Path LIKE ?Path";
                        parameter = parameters.Add("Path", MySqlDbType.VarChar);
                        parameter.Value = path;
                    }

                    command.CommandText += orderBy;
                    reader = (MySqlDataReader)command.ExecuteReader(CommandBehavior.SequentialAccess);
                    PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
                    long recordCount = 0;
                    long lBound = pageIndex * pageSize;
                    long uBound = lBound + pageSize;

                    while(reader.Read()) {
                        recordCount++;
                        if (recordCount <= lBound || recordCount > uBound) {
                            continue;
                        }

                        string returnedPath = reader.GetString(0);
                        DateTime lastUpdatedDate = reader.GetDateTime(1);
                        int size = reader.GetInt32(2);

                        // Create temp info since we need to retrieve the corresponding personalization size and count later
                        stateInfoCollection.Add(new SharedPersonalizationStateInfo(returnedPath, lastUpdatedDate, size, -1, -1));
                    }
                    totalRecords = (int) recordCount;

                    // We need to close the reader in order to make other queries
                    reader.Close();
                    command = new MySqlCommand(findUserState, connection);
                    parameters = (MySqlParameterCollection)command.Parameters;

                    parameter = parameters.Add("ApplicationId", MySqlDbType.VarChar);
                    parameter.Value = appId;
                    parameter = parameters.Add("Path", MySqlDbType.VarChar);
                    PersonalizationStateInfoCollection sharedStateInfoCollection = new PersonalizationStateInfoCollection();

                    foreach (PersonalizationStateInfo stateInfo in stateInfoCollection) {
                        parameter.Value = stateInfo.Path;

                        reader = (MySqlDataReader)command.ExecuteReader(CommandBehavior.SequentialAccess);
                        reader.Read();
                        int sizeOfPersonalizations = Convert.ToInt32(reader.GetValue(0), CultureInfo.InvariantCulture);
                        int countOfPersonalizations = reader.GetInt32(1);
                        reader.Close();
                        sharedStateInfoCollection.Add(new SharedPersonalizationStateInfo(
                                                            stateInfo.Path, stateInfo.LastUpdatedDate,
                                                            stateInfo.Size, sizeOfPersonalizations, countOfPersonalizations));
                    }

                    return sharedStateInfoCollection;
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }

                    if (reader != null) {
                        reader.Close();
                    }
                }
            }
            catch {
                throw;
            }
        }

        public override PersonalizationStateInfoCollection FindState(PersonalizationScope scope,
                                                                     PersonalizationStateQuery query,
                                                                     int pageIndex,
                                                                     int pageSize,
                                                                     out int totalRecords) {
            PersonalizationProviderHelper.CheckPersonalizationScope(scope);
            PersonalizationProviderHelper.CheckPageIndexAndSize(pageIndex, pageSize);

            if (scope == PersonalizationScope.Shared) {
                string pathToMatch = null;
                if (query != null) {
                    pathToMatch = PersonalizationProviderHelper.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, MaxStringLength);
                }
                return FindSharedState(pathToMatch, pageIndex, pageSize, out totalRecords);
            }
            else {
                string pathToMatch = null;
                DateTime inactiveSinceDate = DateTime.MinValue;
                string usernameToMatch = null;
                if (query != null) {
                    pathToMatch = PersonalizationProviderHelper.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, MaxStringLength);
                    inactiveSinceDate = query.UserInactiveSinceDate;
                    usernameToMatch = PersonalizationProviderHelper.CheckAndTrimString(
                                            query.UsernameToMatch, "query.UsernameToMatch", false, MaxStringLength);
                }

                return FindUserState(pathToMatch, inactiveSinceDate, usernameToMatch,
                                     pageIndex, pageSize, out totalRecords);
            }
        }

        private PersonalizationStateInfoCollection FindUserState(string path,
                                                                 DateTime inactiveSinceDate,
                                                                 string username,
                                                                 int pageIndex,
                                                                 int pageSize,
                                                                 out int totalRecords) {
            const string findUserState =
                "SELECT Paths.Path, PerUser.LastUpdatedDate, LEN(PerUser.PageSettings), Users.UserName, Users.LastActivityDate" +
                " FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
                " WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
                " AND Paths.ApplicationId = ?ApplicationId";
            const string orderBy = " ORDER BY Paths.Path ASC, Users.UserName ASC";

            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            MySqlDataReader reader = null;
            totalRecords = 0;

            
            try {
                try {
                    MySqlParameter parameter;
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;
                    MySqlCommand command = new MySqlCommand();
                    command.Connection = connection;
                    MySqlParameterCollection parameters = (MySqlParameterCollection)command.Parameters;

                    Guid appId = GetApplicationID(connectionHolder);
                    parameter = parameters.Add("ApplicationId", MySqlDbType.VarChar);
                    parameter.Value = appId;

                    command.CommandText = findUserState;
                    if (inactiveSinceDate != DateTime.MinValue) {
                        command.CommandText += " AND Users.LastActivityDate <= ?InactiveSinceDate";

                        // Note: MySql provider does not handle datetime that has non-
                        // zero millisecond, so it needs to be rounded up.
                        parameter = parameters.Add("InactiveSinceDate", MySqlDbType.Timestamp);
                        parameter.Value = new DateTime(inactiveSinceDate.Year, inactiveSinceDate.Month, inactiveSinceDate.Day,
                                                       inactiveSinceDate.Hour, inactiveSinceDate.Minute, inactiveSinceDate.Second);
                    }

                    if (path != null) {
                        command.CommandText += " AND Paths.Path LIKE ?Path";
                        parameter = parameters.Add("Path", MySqlDbType.VarChar);
                        parameter.Value = path;
                    }

                    if (username != null) {
                        command.CommandText += " AND Users.UserName LIKE ?UserName";
                        parameter = parameters.Add("UserName", MySqlDbType.VarChar);
                        parameter.Value = username;
                    }

                    command.CommandText += orderBy;
                    reader = (MySqlDataReader)command.ExecuteReader(CommandBehavior.SequentialAccess);
                    PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
                    long recordCount = 0;
                    long lBound = pageIndex * pageSize;
                    long uBound = lBound + pageSize;

                    while(reader.Read()) {
                        recordCount++;
                        if (recordCount <= lBound || recordCount > uBound) {
                            continue;
                        }

                        string returnedPath = reader.GetString(0);
                        DateTime lastUpdatedDate = reader.GetDateTime(1);
                        int size = reader.GetInt32(2);
                        string returnedUsername = reader.GetString(3);
                        DateTime lastActivityDate = reader.GetDateTime(4);
                        stateInfoCollection.Add(new UserPersonalizationStateInfo(
                                                        returnedPath, lastUpdatedDate,
                                                        size, returnedUsername, lastActivityDate));
                    }
                    totalRecords = (int) recordCount;
                    return stateInfoCollection;
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }

                    if (reader != null) {
                        reader.Close();
                    }
                }
            }
            catch {
                throw;
            }
        }

        private MySqlConnectionHolder GetConnectionHolder() {
            MySqlConnection connection = null;
            MySqlConnectionHolder connectionHolder = MySqlConnectionHelper.GetConnection(_ConnectionStringName, true);

            if (connectionHolder != null) {
                connection = connectionHolder.Connection;
            }
            if (connection == null) {
                throw new ProviderException("PersonalizationProvider cannot MySql: "+Name);
            }

            return connectionHolder;
        }

        private int GetCountOfSharedState(string path) {
            string getSharedStateCount =
                "SELECT COUNT(*)" +
                " FROM aspnet_PagePersonalizationAllUsers AllUsers, aspnet_Paths Paths" +
                " WHERE AllUsers.PathId = Paths.PathId AND Paths.ApplicationId = ?ApplicationId";

            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            int count = 0;
           
            try {
                try {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;

                    MySqlCommand command = new MySqlCommand();
                    command.Connection = connection;
                    MySqlParameterCollection parameters = (MySqlParameterCollection)command.Parameters;
                    MySqlParameter parameter;

                    Guid appId = GetApplicationID(connectionHolder);
                    parameter = parameters.Add("ApplicationId", MySqlDbType.VarChar);
                    parameter.Value = appId;

                    if (path != null) {
                        getSharedStateCount += " AND Paths.Path LIKE ?Path";
                        parameter = parameters.Add("Path", MySqlDbType.VarChar);
                        parameter.Value = path;
                    }
                    command.CommandText = getSharedStateCount;

                    object result = command.ExecuteScalar();
                    if ((result != null) && (result is Int32)) {
                        count = (Int32)result;
                    }
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch {
                throw;
            }

            return count;
        }

        public override int GetCountOfState(PersonalizationScope scope, PersonalizationStateQuery query) {
            PersonalizationProviderHelper.CheckPersonalizationScope(scope);
            if (scope == PersonalizationScope.Shared) {
                string pathToMatch = null;
                if (query != null) {
                    pathToMatch = PersonalizationProviderHelper.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, MaxStringLength);
                }
                return GetCountOfSharedState(pathToMatch);
            }
            else {
                string pathToMatch = null;
                DateTime userInactiveSinceDate = DateTime.MinValue;
                string usernameToMatch = null;
                if (query != null) {
                    pathToMatch = PersonalizationProviderHelper.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, MaxStringLength);
                    userInactiveSinceDate = query.UserInactiveSinceDate;
                    usernameToMatch = PersonalizationProviderHelper.CheckAndTrimString(
                                            query.UsernameToMatch, "query.UsernameToMatch", false, MaxStringLength);
                }
                return GetCountOfUserState(pathToMatch, userInactiveSinceDate, usernameToMatch);
            }
        }

        private int GetCountOfUserState(string path, DateTime inactiveSinceDate, string username) {
            string getUserStateCount =
                "SELECT COUNT(*)" +
                " FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
                " WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
                " AND Paths.ApplicationId = ?ApplicationId";

            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            int count = 0;

            
            try {
                try {
                    MySqlParameter parameter;
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;
                    MySqlCommand command = new MySqlCommand();
                    command.Connection = connection;
                    MySqlParameterCollection parameters = (MySqlParameterCollection)command.Parameters;

                    Guid appId = GetApplicationID(connectionHolder);
                    parameter = parameters.Add("ApplicationId", MySqlDbType.VarChar);
                    parameter.Value = appId;

                    if (path != null) {
                        getUserStateCount += " AND Paths.Path LIKE ?Path";
                        parameter = parameters.Add("Path", MySqlDbType.VarChar);
                        parameter.Value = path;
                    }

                    if (username != null) {
                        getUserStateCount += " AND Users.UserName LIKE ?UserName";
                        parameter = parameters.Add("UserName", MySqlDbType.VarChar);
                        parameter.Value = username;
                    }

                    if (inactiveSinceDate != DateTime.MinValue) {
                        getUserStateCount += " AND Users.LastActivityDate <= ?InactiveSinceDate";

                        // Note: MySql provider does not handle datetime that has non-
                        // zero millisecond, so it needs to be rounded up.
                        parameter = parameters.Add("InactiveSinceDate", MySqlDbType.Timestamp);
                        parameter.Value = new DateTime(inactiveSinceDate.Year, inactiveSinceDate.Month, inactiveSinceDate.Day,
                                                       inactiveSinceDate.Hour, inactiveSinceDate.Minute, inactiveSinceDate.Second);
                    }

                    command.CommandText = getUserStateCount;

                    object result = command.ExecuteScalar();
                    if ((result != null) && (result is Int32)) {
                        count = (Int32)result;
                    }
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch {
                throw;
            }

            return count;
        }

        public override void Initialize(string name, NameValueCollection configSettings) {
            base.Initialize(name, configSettings);

            // If not available, the default value is set in the get MySqlor of ApplicationName
            _applicationName = configSettings["applicationName"];
            if (_applicationName != null) {
                configSettings.Remove("applicationName");

                if (_applicationName.Length > MaxStringLength) {
                    throw new ProviderException("ApplicationName exceeded max length of " + MaxStringLength);
                }
            }

            _ConnectionStringName = configSettings["connectionStringName"];
            if (_ConnectionStringName == null || _ConnectionStringName.Length < 1)
                throw new ProviderException("Connection name not specified");

            string s_conn = MySqlConnectionHelper.GetConnectionString(_ConnectionStringName, true);
            if (s_conn == null || s_conn.Length < 1)
            {
                throw new ProviderException("Connection string not found: " + _ConnectionStringName);
            }

            if (!MySqlConnectionHelper.IsValidConnectionString(s_conn))
            {
                throw new ProviderException("Provider invalid connectionString: " + _ConnectionStringName);
            }

            if (configSettings.Count > 0) {
                string invalidAttributeName = configSettings.GetKey(0);

                throw new ProviderException("Unknown attribute: "+invalidAttributeName+name);
            }
        }

        private string LoadPersonalizationBlob(MySqlConnection connection, Guid pathID)
        {
            MySqlCommand lookupCommand = new MySqlCommand("SELECT PageSettings FROM aspnet_PagePersonalizationAllUsers WHERE PathId = ?PathId", connection);
            lookupCommand.Parameters.Add(new MySqlParameter("?PathId", pathID));

            object lookupResult = lookupCommand.ExecuteScalar();
            if ((lookupResult != null) && (lookupResult is string)) {
                return (string)lookupResult;
            }

            return null;
        }

        private string LoadPersonalizationBlob(MySqlConnection connection, Guid pathID, Guid userID)
        {
            MySqlCommand updateCommand = new MySqlCommand("UPDATE  aspnet_Users " +
                                                          "SET     LastActivityDate = ?LastActivityDate " +
                                                          "WHERE   UserId = ?UserId", connection);
            updateCommand.Parameters.Add(CreateDateTimeMySqlParameter("?LastActivityDate", DateTime.Now));
            updateCommand.Parameters.Add(new MySqlParameter("?UserId", userID));
            updateCommand.ExecuteNonQuery();

            MySqlCommand lookupCommand = new MySqlCommand("SELECT PageSettings FROM aspnet_PagePersonalizationPerUser WHERE PathId = ?PathId AND UserId = ?UserId", connection);
            lookupCommand.Parameters.Add(new MySqlParameter("?PathId", pathID));
            lookupCommand.Parameters.Add(new MySqlParameter("?UserId", userID));

            object lookupResult = lookupCommand.ExecuteScalar();
            if ((lookupResult != null) && (lookupResult is string)) {
                return (string)lookupResult;
            }

            return null;
        }

        protected override void LoadPersonalizationBlobs(WebPartManager webPartManager, string path, string userName, ref byte[] sharedDataBlob, ref byte[] userDataBlob) {
            sharedDataBlob = null;
            userDataBlob = null;

            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;

            
            try {
                try {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;

                    Guid applicationID = GetApplicationID(connectionHolder);
                    if (applicationID != Guid.Empty) {
                        Guid pathID = MySqlConnectionHelper.GetPathID(connection, applicationID, path);
                        if (pathID != Guid.Empty) {
                            string sharedDataValue = LoadPersonalizationBlob(connection, pathID);
                            sharedDataBlob = Deserialize(sharedDataValue);

                            if (userName != null) {
                                Guid userID = MySqlConnectionHelper.GetUserID(connection, applicationID, userName);
                                if (userID != Guid.Empty) {
                                    string userDataValue = LoadPersonalizationBlob(connection, pathID, userID);
                                    userDataBlob = Deserialize(userDataValue);
                                }
                            }
                        }
                    }
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch {
                throw;
            }
        }

        private void ResetPersonalizationBlob(MySqlConnection connection, Guid pathID)
        {
            MySqlCommand lookupCommand = new MySqlCommand("DELETE FROM aspnet_PagePersonalizationAllUsers WHERE PathId = ?PathId", connection);

            lookupCommand.Parameters.Add(new MySqlParameter("?PathId", pathID));
            lookupCommand.ExecuteNonQuery();
        }

        private void ResetPersonalizationBlob(MySqlConnection connection, Guid pathID, Guid userID)
        {
            MySqlCommand lookupCommand = new MySqlCommand("DELETE FROM aspnet_PagePersonalizationPerUser WHERE PathId = ?PathId AND UserId = ?UserId", connection);

            lookupCommand.Parameters.Add(new MySqlParameter("?PathId", pathID));
            lookupCommand.Parameters.Add(new MySqlParameter("?UserId", userID));
            lookupCommand.ExecuteNonQuery();
        }

        protected override void ResetPersonalizationBlob(WebPartManager webPartManager, string path, string userName) {
            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;

            
            try {
                try {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;

                    Guid applicationID = GetApplicationID(connectionHolder);
                    if (applicationID != Guid.Empty) {
                        Guid pathID = MySqlConnectionHelper.GetPathID(connection, applicationID, path);

                        if (pathID != Guid.Empty) {
                            if (String.IsNullOrEmpty(userName)) {
                                ResetPersonalizationBlob(connection, pathID);
                            }
                            else {
                                Guid userID = MySqlConnectionHelper.GetUserID(connection, applicationID, userName);
                                if (userID != Guid.Empty) {
                                    ResetPersonalizationBlob(connection, pathID, userID);
                                }
                            }
                        }
                    }
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch {
                throw;
            }
        }

        private int ResetAllState(string getStateCountQuery, string deleteStateQuery) {
            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            int count = 0;

            
            try {
                try {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;

                    // Get the count of records that would be deleted
                    MySqlCommand command = new MySqlCommand(getStateCountQuery, connection);
                    object lookupResult = command.ExecuteScalar();
                    if ((lookupResult != null) && (lookupResult is Int32)) {
                        count = (Int32)lookupResult;
                    }

                    // Do the actual deletion
                    command.CommandText = deleteStateQuery;
                    command.ExecuteNonQuery();
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch {
                throw;
            }

            return count;
        }

        private int ResetSharedState(string[] paths) {
            if (paths == null) {
                const string deleteAllSharedState = "DELETE FROM aspnet_PagePersonalizationAllUsers";
                const string getAllSharedStateCount = "SELECT COUNT(*) FROM aspnet_PagePersonalizationAllUsers";
                return ResetAllState(getAllSharedStateCount, deleteAllSharedState);
            }
            else {
                return ResetStatePerPaths("aspnet_PagePersonalizationAllUsers", paths);
            }
        }

        public override int ResetUserState(string path, DateTime userInactiveSinceDate) {
            path = PersonalizationProviderHelper.CheckAndTrimString(path, "path", false, MaxStringLength);

            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            int count = 0;

            
            try {
                try {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;

                    // Special note: MySqlProvider requires the parameters to be added
                    // in the same order as appearing in the query text.

                    string getDeleteUserStateCount =
                        "SELECT COUNT(*)" +
                        " FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
                        " WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
                        " AND Paths.ApplicationId = ?ApplicationId" +
                        " AND Users.LastActivityDate <= ?InactiveSinceDate";

                    string deleteUserState =
                        "DELETE FROM aspnet_PagePersonalizationPerUser" +
                        " WHERE Id IN (SELECT PerUser.Id " +
                                     " FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
                                     " WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
                                     " AND Paths.ApplicationId = ?ApplicationId" +
                                     " AND Users.LastActivityDate <= ?InactiveSinceDate";

                    // Get the count of records that would be deleted
                    MySqlCommand command = new MySqlCommand();
                    command.Connection = connection;
                    MySqlParameterCollection parameters = (MySqlParameterCollection)command.Parameters;
                    MySqlParameter parameter;

                    Guid appId = GetApplicationID(connectionHolder);
                    parameter = parameters.Add("ApplicationId", MySqlDbType.Int32);
                    parameter.Value = appId;

                    // Note: MySql provider does not handle datetime that has non-
                    // zero millisecond, so it needs to be rounded up.
                    parameter = parameters.Add("InactiveSinceDate", MySqlDbType.Timestamp);
                    parameter.Value = new DateTime(userInactiveSinceDate.Year, userInactiveSinceDate.Month, userInactiveSinceDate.Day,
                                                   userInactiveSinceDate.Hour, userInactiveSinceDate.Minute, userInactiveSinceDate.Second);

                    if (path != null) {
                        const string pathParamQueryText = " AND Paths.Path = ?Path";
                        getDeleteUserStateCount += pathParamQueryText;
                        deleteUserState += pathParamQueryText;
                        parameter = parameters.Add("Path", MySqlDbType.VarChar);
                        parameter.Value = path;
                    }
                    deleteUserState += ")";

                    command.CommandText = getDeleteUserStateCount;

                    object lookupResult = command.ExecuteScalar();
                    if ((lookupResult != null) && (lookupResult is Int32)) {
                        count = (Int32)lookupResult;
                        if (count > 0) {
                            // Do the actual deletion
                            command.CommandText = deleteUserState;
                            command.ExecuteNonQuery();
                        }
                    }
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch {
                throw;
            }

            return count;
        }

        public override int ResetState(PersonalizationScope scope, string[] paths, string[] usernames) {
            PersonalizationProviderHelper.CheckPersonalizationScope(scope);
            paths = PersonalizationProviderHelper.CheckAndTrimNonEmptyStringEntries(paths, "paths", false, false, MaxStringLength);
            usernames = PersonalizationProviderHelper.CheckAndTrimNonEmptyStringEntries(usernames, "usernames", false, true, MaxStringLength);

            if (scope == PersonalizationScope.Shared) {
                PersonalizationProviderHelper.CheckUsernamesInSharedScope(usernames);
                return ResetSharedState(paths);
            }
            else {
                PersonalizationProviderHelper.CheckOnlyOnePathWithUsers(paths, usernames);
                return ResetUserState(paths, usernames);
            }
        }

        private int ResetUserState(string[] paths, string[] usernames) {
            int count = 0;
            bool hasPaths = !(paths == null || paths.Length == 0);
            bool hasUsernames = !(usernames == null || usernames.Length == 0);

            if (!hasPaths && !hasUsernames) {
                const string deleteAllUserState = "DELETE FROM aspnet_PagePersonalizationPerUser";
                const string getAllUserStateCount = "SELECT COUNT(*) FROM aspnet_PagePersonalizationPerUser";
                count = ResetAllState(getAllUserStateCount, deleteAllUserState);
            }
            else if (!hasUsernames) {
                count = ResetStatePerPaths("aspnet_PagePersonalizationPerUser", paths);
            }
            else {
                string path = (paths != null) ? paths[0] : null;
                count = ResetUserStatePerUsers(path, usernames);
            }

            return count;
        }

        private int ResetStatePerPaths(string tableName, string [] paths) {
            if (paths == null || paths.Length == 0) {
                return 0;
            }

            int count = 0;
            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            bool beginTransCalled = false;
            
            try {
                try {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;

                    Guid applicationID = GetApplicationID(connectionHolder);
                    if (applicationID != Guid.Empty) {
                        string fromAndWhereClause = " FROM " + tableName + " WHERE PathId = ?PathId";
                        string selectCommandText = "SELECT COUNT(*)" + fromAndWhereClause;
                        string deleteCommandText = "DELETE" + fromAndWhereClause;
                        MySqlCommand command = new MySqlCommand(null, connection);
                        MySqlParameterCollection parameters = (MySqlParameterCollection)command.Parameters;
                        MySqlParameter pathParam = (MySqlParameter)parameters.Add(new MySqlParameter("?PathId", MySqlDbType.Int32));

                        MySqlCommand transCommand = new MySqlCommand("START TRANSACTION", connection);
                        transCommand.ExecuteNonQuery();
                        beginTransCalled = true;

                        foreach (string path in paths) {
                            command.CommandText = selectCommandText;
                            pathParam.Value = MySqlConnectionHelper.GetPathID(connection, applicationID, path);
                            int numOfRecords = (int) command.ExecuteScalar();
                            if (numOfRecords > 0) {
                                command.CommandText = deleteCommandText;
                                command.ExecuteNonQuery();
                                count += numOfRecords;
                            }
                        }

                        transCommand.CommandText = "COMMIT";
                        transCommand.ExecuteNonQuery();
                    }
                }
                catch {
                    try {
                        if (beginTransCalled) {
                            MySqlCommand rollbackCommand = new MySqlCommand("ROLLBACK", connection);
                            rollbackCommand.ExecuteNonQuery();
                        }
                    }
                    catch {
                    }
                    throw;
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch {
                throw;
            }

            return count;
        }

        private int ResetUserStatePerUsers(string path, string [] usernames) {
            int count = 0;
            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            bool beginTransCalled = false;
            
            try {
                try {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;

                    Guid applicationID = GetApplicationID(connectionHolder);
                    if (applicationID != Guid.Empty) {
                        MySqlCommand command = new MySqlCommand(null, connection);
                        MySqlParameterCollection parameters = (MySqlParameterCollection)command.Parameters;
                        MySqlParameter userIdParam = (MySqlParameter)parameters.Add(new MySqlParameter("?UserId", MySqlDbType.Int32));
                        string fromAndWhereClause = " FROM aspnet_PagePersonalizationPerUser WHERE UserId = ?UserId";
                        if (!String.IsNullOrEmpty(path)) {
                            Guid pathId = MySqlConnectionHelper.GetPathID(connection, applicationID, path);
                            fromAndWhereClause += " AND PathId = ?PathId";
                            parameters.Add(new MySqlParameter("?PathId", pathId));
                        }

                        string selectCommandText = "SELECT COUNT(*)" + fromAndWhereClause;
                        string deleteCommandText = "DELETE" + fromAndWhereClause;

                        MySqlCommand transCommand = new MySqlCommand("START TRANSACTION", connection);
                        transCommand.ExecuteNonQuery();
                        beginTransCalled = true;

                        foreach (string username in usernames) {
                            command.CommandText = selectCommandText;
                            userIdParam.Value = MySqlConnectionHelper.GetUserID(connection, applicationID, username);
                            int numOfRecords = (int) command.ExecuteScalar();
                            if (numOfRecords > 0) {
                                command.CommandText = deleteCommandText;
                                command.ExecuteNonQuery();
                                count += numOfRecords;
                            }
                        }

                        transCommand.CommandText = "COMMIT";
                        transCommand.ExecuteNonQuery();
                    }
                }
                catch {
                    try {
                        if (beginTransCalled) {
                            MySqlCommand rollbackCommand = new MySqlCommand("ROLLBACK", connection);
                            rollbackCommand.ExecuteNonQuery();
                        }
                    }
                    catch {
                    }
                    throw;
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch {
                throw;
            }

            return count;
        }

        private void SavePersonalizationBlob(MySqlConnection connection, Guid pathID, string state)
        {
            string currentDate = DateTime.Now.ToString(CultureInfo.InvariantCulture);
            MySqlCommand updateCommand = new MySqlCommand("UPDATE aspnet_PagePersonalizationAllUsers SET PageSettings = ?PageSettings, LastUpdatedDate = ?UpdatedDate WHERE PathId = ?PathId", connection);

            updateCommand.Parameters.Add(new MySqlParameter("?PageSettings", state));
            updateCommand.Parameters.Add(new MySqlParameter("?UpdatedDate", currentDate));
            updateCommand.Parameters.Add(new MySqlParameter("?PathId", pathID));
            if (updateCommand.ExecuteNonQuery() != 1) {
                MySqlCommand insertCommand = new MySqlCommand("INSERT INTO aspnet_PagePersonalizationAllUsers (PathId, PageSettings, LastUpdatedDate) VALUES (?PathId, ?PageSettings, ?UpdatedDate)", connection);

                insertCommand.Parameters.Add(new MySqlParameter("?PathId", pathID));
                insertCommand.Parameters.Add(new MySqlParameter("?PageSettings", state));
                insertCommand.Parameters.Add(new MySqlParameter("?UpdatedDate", currentDate));
                insertCommand.ExecuteNonQuery();
            }
        }

        private void SavePersonalizationBlob(MySqlConnection connection, Guid pathID, Guid userID, string state)
        {
            DateTime now = DateTime.Now;

            MySqlCommand updateCommand = new MySqlCommand("UPDATE  aspnet_Users " +
                                                          "SET     LastActivityDate = ?LastActivityDate " +
                                                          "WHERE   UserId = ?UserId", connection);
            updateCommand.Parameters.Add(CreateDateTimeMySqlParameter("?LastActivityDate", now));
            updateCommand.Parameters.Add(new MySqlParameter("?UserId", userID));
            updateCommand.ExecuteNonQuery();

            string currentDate = now.ToString(CultureInfo.InvariantCulture);
            updateCommand = new MySqlCommand("UPDATE aspnet_PagePersonalizationPerUser SET PageSettings = ?PageSettings, LastUpdatedDate = ?UpdatedDate WHERE PathId = ?PathId AND UserId = ?UserId", connection);

            updateCommand.Parameters.Add(new MySqlParameter("?PageSettings", state));
            updateCommand.Parameters.Add(new MySqlParameter("?UpdatedDate", currentDate));
            updateCommand.Parameters.Add(new MySqlParameter("?PathId", pathID));
            updateCommand.Parameters.Add(new MySqlParameter("?UserId", userID));
            if (updateCommand.ExecuteNonQuery() != 1) {
                MySqlCommand insertCommand = new MySqlCommand("INSERT INTO aspnet_PagePersonalizationPerUser (PathId, UserId, PageSettings, LastUpdatedDate) VALUES (?PathId, ?UserId, ?PageSettings, ?UpdatedDate)", connection);

                insertCommand.Parameters.Add(new MySqlParameter("?PathId", pathID));
                insertCommand.Parameters.Add(new MySqlParameter("?UserId", userID));
                insertCommand.Parameters.Add(new MySqlParameter("?PageSettings", state));
                insertCommand.Parameters.Add(new MySqlParameter("?UpdatedDate", currentDate));
                insertCommand.ExecuteNonQuery();
            }
        }

        protected override void SavePersonalizationBlob(WebPartManager webPartManager, string path, string userName, byte[] dataBlob) {
            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;

            
            try {
                try {
                    string blobValue = Serialize(dataBlob);

                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;

                    Guid applicationID = GetApplicationID(connectionHolder);
                    if (applicationID != Guid.Empty) {
                        Guid pathID = MySqlConnectionHelper.GetPathID(connection, applicationID, path, /* createIfNeeded */ true);

                        if (pathID != Guid.Empty) {
                            if (String.IsNullOrEmpty(userName)) {
                                SavePersonalizationBlob(connection, pathID, blobValue);
                            }
                            else {
                                Guid userID = MySqlConnectionHelper.GetUserID(connection, applicationID, userName, /* createIfNeeded */ true);
                                if (userID != Guid.Empty) {
                                    SavePersonalizationBlob(connection, pathID, userID, blobValue);
                                }
                            }
                        }
                    }
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch {
                throw;
            }
        }

        private string Serialize(byte[] data) {
            if ((data == null) || (data.Length == 0)) {
                return String.Empty;
            }
            return Convert.ToBase64String(data);
        }
    }
}
